using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;

namespace DocLibApp
{
    /// <summary>
    /// abstract class for easier sql query building
    /// </summary>
    public class querybuilder
    {
        //predefined pattern
        private string Pattern_INSERT = "INSERT INTO %TABLENAME% (%FIELDLIST%) VALUES (%VALUELIST%)";
        private string Pattern_SELECT = "SELECT %FIELDLIST% FROM %TABLENAME% WHERE %WHERECLAUSE%";
        private string Pattern_UPDATE = "UPDATE %TABLENAME% SET %UPDATELIST% WHERE (%WHERECLAUSE%)";
        private string Pattern_UPDATE_WHERE_IN = "UPDATE %TABLENAME% SET %UPDATELIST% WHERE [%FIELDNAME%] IN (%WHERE_IN_LIST%')";
        private string Pattern_DELETE = "DELETE FROM %TABLENAME% WHERE (%WHERECLAUSE%)";

        //private variables
        private string sTableName = "";
        private string sSQLStatement = "";
        private string sOrderBy = "";
        private string sWhereOperator = "AND";

        //default definition
        private string sFieldEncStart = "[";
        private string sFieldEncEnd = "]";
        private string sValueEncStart = "'";
        private string sValueEncEnd = "'";

        public querybuilder()
        {
        }

        #region Public Properties & Functions
        /// <summary>
        /// set / get tablename used in query
        /// </summary>
        public string TableName
        {
            set
            {
                sTableName = value;
            }
            get
            {
                return sTableName;
            }
        }

        /// <summary>
        /// set / get query used in query builder
        /// </summary>
        public string SQL
        {
            set
            {
                sSQLStatement = value;
            }
            get
            {
                return sSQLStatement;
            }
        }

        /// <summary>
        /// set / get the operator used in WHERE clause (AND / OR)
        /// </summary>
        public string WhereOperator
        {
            set
            {
                sWhereOperator = value;
            }
            get
            {
                return sWhereOperator;
            }
        }

        public string OrderBy
        {
            set
            {
                sOrderBy = value;
            }
            get
            {
                return sOrderBy;
            }
        }

        /// <summary>
        /// if a record exist based on the constructed query
        /// </summary>
        /// <returns></returns>
        public bool IfRecordExist()
        {
            return FormMain.DocLib_SQLite.IfRecordExistUseCriteria(sSQLStatement);
        }

        public int GetRecordCount()
        {
            int count = 0;

            if (!string.IsNullOrEmpty(sSQLStatement))
                count = FormMain.DocLib_SQLite.GetRecordCountUseCriteria(sSQLStatement);

            return count;
        }

        public string GetDataString()
        {
            string res = "";

            try
            {
                if (this.sSQLStatement.Trim() != "")
                {
                    res = FormMain.DocLib_SQLite.GetDataString(sSQLStatement);
                }
            }
            catch (System.Exception ex)
            {
                HandleEx(ex);                
            }

            return res;
        }

        public System.Collections.Hashtable GetDataOneRowInHash()
        {
            System.Collections.Hashtable ht = new Hashtable();
            ht = FormMain.DocLib_SQLite.GetRowWithDataReaderIntoHash(sSQLStatement);
            return ht;
        }

        /// <summary>
        /// data result is only one field, could be multiple records
        /// </summary>
        /// <returns></returns>
        public List<string> GetData_OneColumn()
        {
            List<string> res = new List<string>();

            if (!string.IsNullOrEmpty(sSQLStatement))
            {
                System.Collections.ArrayList alist = FormMain.DocLib_SQLite.GetRowsWithDataReader(sSQLStatement, false, false);
                foreach (object obj in alist)
                {
                    if (obj is List<string>)
                    {
                        List<string> aRow = obj as List<string>;
                        if (aRow.Count > 0 && !string.IsNullOrEmpty(aRow[0]))
                        {
                            res.Add(aRow[0]);
                        }
                    }
                }
            }

            return res;
        }

        public System.Collections.ArrayList GetDataRow_ArrayList(bool IncludeFieldName)
        {
            System.Collections.ArrayList alist = new ArrayList();

            if (!string.IsNullOrEmpty(sSQLStatement))
            {
                alist = FormMain.DocLib_SQLite.GetRowsWithDataReader(sSQLStatement, false, IncludeFieldName);
            }

            return alist;
        }

        public string RunQuery()
        {
            string res = "";

            try
            {
                if (this.sSQLStatement.Trim() != "")
                {
                    FormMain.DocLib_SQLite.ExecuteSQL(sSQLStatement);
                }
            }
            catch (System.Exception ex)
            {
                res = ex.Message;
                HandleEx(ex);
            }

            return res;
        }
        #endregion //EO Public Properties & Functions

        #region Query Building Functions
        #region INSERT
        /// <summary>
        /// generate a insert sql statement from list of field /value hast table (INSERT INTO doc_main (doc_id, category) values ('32434','fax'))
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        public string Get_Insert(Hashtable ht)
        {
            //INSERT INTO %TABLENAME% (%FIELDLIST%) VALUES (%VALUELIST%)
            string fieldlist = "", valuelist = "";

            BuildTableName(Pattern_INSERT);

            foreach (string key in ht.Keys)
            {
                string value = ht[key].ToString().Trim();

                fieldlist = InsertToList(fieldlist, EncField(key) );
                valuelist = InsertToList(valuelist, EncValue(value) );
            }

            sSQLStatement = sSQLStatement.Replace("%FIELDLIST%", fieldlist).Replace("%VALUELIST%", valuelist);

            return sSQLStatement;
        }
        #endregion //EO INSERT

        #region UPDATE
        /// <summary>
        /// Generate UPDATE sql query [UPDATE table SET (FIELD=VALUE) WHERE (FIELD = VALUE)]
        /// </summary>
        /// <param name="htUpdate">Update field = value</param>
        /// <param name="htWhere">Where criteria field = value</param>
        /// <returns></returns>
        public string Get_Update(Hashtable htUpdate, Hashtable htWhere)
        {
            BuildTableName(Pattern_UPDATE);

            string WhereList = "", UpdateList = "";

            foreach (string key in htWhere.Keys)
            {
                string value = htWhere[key].ToString().Trim();

                if (String.IsNullOrEmpty(WhereList))
                    WhereList = EncField(key) + " = " + EncValue(value);
                else
                    WhereList += " " + sWhereOperator + " " + EncField(key) + " = " + EncValue(value);
            }

            foreach (string key in htUpdate.Keys)
            {
                string value = htUpdate[key].ToString().Trim();

                if (String.IsNullOrEmpty(UpdateList))
                    UpdateList = EncField(key) + " = " + EncValue(value);
                else
                    UpdateList += " , " + EncField(key) + " = " + EncValue(value);
            }

            sSQLStatement = sSQLStatement.Replace("%UPDATELIST%", UpdateList).Replace("%WHERECLAUSE%", WhereList);

            return sSQLStatement;
        }

        /// <summary>
        /// Generate UPDATE sql query {UPDATE doc_main SET (Category = 'FAX') WHERE [doc_id] in ('2445','3455')}
        /// 
        /// </summary>
        /// <param name="FieldName">Where FieldName in</param>
        /// <param name="htUpdateList"></param>
        /// <param name="WhereIn"></param>
        /// <returns></returns>
        public string Get_Update(Hashtable htUpdateList, string FieldName, List<string> WhereIn)
        {
            //UPDATE %TABLENAME% SET %UPDATELIST% WHERE [%FIELDNAME%] IN (%WHERE_IN_LIST%')";
            BuildTableName(Pattern_UPDATE_WHERE_IN);
            sSQLStatement = sSQLStatement.Replace("%FIELDNAME%", FieldName);

            string WhereList = "", UpdateList = "";

            foreach (string value in WhereIn)
            {
                if (string.IsNullOrEmpty(WhereList))
                    WhereList = EncValue(value);
                else
                    WhereList += ", " + EncValue(value);
            }

            foreach (string key in htUpdateList.Keys)
            {
                string value = htUpdateList[key].ToString().Trim();

                if (String.IsNullOrEmpty(UpdateList))
                    UpdateList = EncField(key) + " = " +  EncValue(value);
                else
                    UpdateList += " , " +  EncField(key) + " = " + EncValue(value);
            }

            sSQLStatement = sSQLStatement.Replace("%UPDATELIST%", UpdateList).Replace("%WHERE_IN_LIST%", WhereList);

            return sSQLStatement;
        }
        #endregion //EO UPDATE

        #region SELECT
        /// <summary>
        /// Generate select sql (select doc_id, category, type from doc_main where doc_id = '3434' AND category = 'FAX')
        /// </summary>
        /// <param name="FieldList">Fields to return</param>
        /// <param name="ht">Search criteria key = value</param>
        /// <returns></returns>
        public string Get_Select(List<string> FieldList, System.Collections.Hashtable ht)
        {
            string whereclause = "", sFieldList = "";

            //Pattern_SELECT = "SELECT %FIELDLIST% FROM %TABLENAME% WHERE %WHERECLAUSE%";
            BuildTableName(Pattern_SELECT);

            if (ht.Count > 0)
            {
                string sCompareOperator = "";

                foreach (string key in ht.Keys)
                {
                    string value = ht[key].ToString().Trim();

                    //if select criteria value contains %, change operator from = to like automatically
                    if (value.IndexOf("%") > -1)
                        sCompareOperator = "LIKE";
                    else
                        sCompareOperator = "=";

                    if (String.IsNullOrEmpty(whereclause))
                    {
                        whereclause = EncField(key) + " " + sCompareOperator + " " + EncValue(value);
                    }
                    else
                    {
                        whereclause += " " + sWhereOperator + " " + EncField(key) + " " + sCompareOperator + " " + EncValue(value);
                    }
                }
            }

            if (FieldList.Count > 0)
            {
                foreach (string field in FieldList)
                {
                    if (string.IsNullOrEmpty(sFieldList))
                        sFieldList = EncField(field);
                    else
                        sFieldList += ", " + EncField(field);
                }
            }
            else //if there is no field defined, return all fields * as default
            {
                sFieldList = "*";
            }

            //Pattern_SELECT = "SELECT %FIELDLIST% FROM %TABLENAME% WHERE %WHERECLAUSE%";
            sSQLStatement = sSQLStatement.Replace("%FIELDLIST%", sFieldList);

            if (!string.IsNullOrEmpty(whereclause))
                sSQLStatement = sSQLStatement.Replace("%WHERECLAUSE%", whereclause);
            else
                sSQLStatement = sSQLStatement.Replace("WHERE %WHERECLAUSE%", "");

            return sSQLStatement;

        }

        /// <summary>
        /// Select * FROM table WHERE field = value
        /// </summary>
        /// <param name="field"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public string Get_Select(string field, string value)
        {
            BuildTableName(Pattern_SELECT);
            sSQLStatement = sSQLStatement.Replace("%FIELDLIST%", "*");
            sSQLStatement = sSQLStatement.Replace("%WHERECLAUSE%", EncField(field) + " = " + EncValue(value));
            return sSQLStatement;
        }

        /// <summary>
        /// Generate a select query from hash table of search parameter (SELECT * FROM table Where key = value etc)
        /// </summary>
        /// <param name="ht">contains column = value pairs</param>
        /// <returns>sql query</returns>
        public string Get_Select(System.Collections.Hashtable ht)
        {
            string sFieldList = "*", whereclause = "", sCompOp = "";

            BuildTableName(Pattern_SELECT);
            sSQLStatement = sSQLStatement.Replace("%FIELDLIST%", sFieldList);

            if (ht.Count > 0)
            {
                foreach (string key in ht.Keys)
                {
                    string value = ht[key].ToString().Trim();

                    if (value.IndexOf("%") > -1)
                        sCompOp = "LIKE";
                    else
                        sCompOp = "=";

                    if (String.IsNullOrEmpty(whereclause))
                    {
                        whereclause = EncField(key) + " " + sCompOp + " " + EncValue(value);
                    }
                    else
                    {
                        whereclause += " " + sWhereOperator + " " + EncField(key) + " " + sCompOp + " " + EncValue(value);
                    }
                }

                sSQLStatement = sSQLStatement.Replace("%WHERECLAUSE%", whereclause);
            }
            else
            {
                sSQLStatement = "SELECT * FROM " + sTableName;
            }

            return sSQLStatement;
        }
        #endregion //EO SELECT

        #region DELETE
        /// <summary>
        /// Delete FROM table WHERE field = value
        /// </summary>
        /// <param name="Field"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        public string Get_Delete(string Field, string Value)
        {
            BuildTableName(Pattern_DELETE);
            //DELETE FROM %TABLENAME% WHERE (%WHERECLAUSE%)
            sSQLStatement = sSQLStatement.Replace("%WHERECLAUSE%", EncField(Field) + " = " + EncValue(Value));
            return sSQLStatement;
        }
        /// <summary>
        /// Generate DELETE sql (DELETE FROM doc_main where doc_id = '2434' AND category = 'FAX')
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        public string Get_Delete(Hashtable ht)
        {
            //DELETE FROM %TABLENAME% WHERE (%WHERECLAUSE%)
            string whereClause = "";

            BuildTableName(Pattern_DELETE);

            foreach (string key in ht.Keys)
            {
                string value = ht[key].ToString().Trim();

                if (string.IsNullOrEmpty(whereClause))
                    whereClause = EncField(key) + " = " + EncValue(value);
                else
                    whereClause += " " + sWhereOperator + " " + EncField(key) + " = " + EncValue(value);
            }

            sSQLStatement = sSQLStatement.Replace("%WHERECLAUSE%", whereClause);

            return sSQLStatement;
        }
        #endregion //EO DELETE
        #endregion //EO Query Building Functions

        #region Private Functions
        private void HandleEx(System.Exception ex)
        {
            eh myeh = new eh(true);
            myeh.WriteExpLog("querybuilder.cs", ex);
            myeh.ShowExp(ex);
        }

        private void BuildTableName(string pattern)
        {
            sSQLStatement = pattern.Replace("%TABLENAME%", sTableName);
        }

        private void BuildOrderBy()
        {
        }

        private string InsertToList(string list, string value)
        {
            if (String.IsNullOrEmpty(list))
                list = value;
            else
                list += ", " + value;

            return list;
        }

        /// <summary>
        /// enclose a field name in defined enclosure
        /// </summary>
        /// <param name="fieldname"></param>
        /// <returns></returns>
        private string EncField(string fieldname)
        {
            return sFieldEncStart + fieldname + sFieldEncEnd;
        }

        /// <summary>
        /// enclose a value in defined ecnlosure
        /// </summary>
        /// <param name="Value"></param>
        /// <returns></returns>
        private string EncValue(string Value)
        {
            if (Value.IndexOf("'") > -1)
                Value = Value.Replace("'", "''");

            return sValueEncStart + Value + sValueEncEnd;
        }
        #endregion //EO Private Functions
    }
}
